package com.atguigu.demo.common;

import cn.hutool.core.lang.func.Func1;
import com.atguigu.demo.mapper.UserMapper;
import com.atguigu.demo.pojo.DepartmentEntity;
import com.atguigu.demo.pojo.UserEntity;
import com.zaxxer.hikari.HikariDataSource;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.H2Style;
import org.beetl.sql.core.query.LambdaQuery;
import org.beetl.sql.core.query.Query;
import org.beetl.sql.ext.DBInitHelper;
import org.beetl.sql.ext.DebugInterceptor;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class QuickTest<R> {
	private static   DataSource datasource() {
		HikariDataSource ds = new HikariDataSource();
    //内存数据库
		ds.setJdbcUrl("jdbc:h2:mem:dbtest;DB_CLOSE_ON_EXIT=FALSE");
		ds.setUsername("sa");
		ds.setPassword("");
		ds.setDriverClassName("org.h2.Driver");
		return ds;
	}

	private  static SQLManager getSQLManager(){
    //得到一个数据源
		DataSource dataSource = datasource();
    //得到一个ConnectionSource， 单数据源
		ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
    //SQLManagerBuilder 唯一必须的参数就是ConnectionSource
		SQLManagerBuilder builder = new SQLManagerBuilder(source);
    //命名转化，数据库表和列名下划线风格，转化成Java对应的首字母大写，比如create_time 对应ceateTime
		builder.setNc(new UnderlinedNameConversion());
    //拦截器，非必须，这里设置一个debug拦截器，可以详细查看执行后的sql和sql参数
		builder.setInters(new Interceptor[]{new DebugInterceptor()});
    //数据库风格，因为用的是H2,所以使用H2Style,
		builder.setDbStyle(new H2Style());
		SQLManager sqlManager = builder.build();
		return sqlManager;
	}
	
    public static void main(String[] args) throws Exception {
        SQLManager sqlManager = getSQLManager();
        //初始化数据脚本，执行后，内存数据库将有一个sys_user表和模拟数据
        DBInitHelper.executeSqlScript(sqlManager,"db/schema.sql");

       // 得到数据库的所有表
//        Set<String> all =  sqlManager.getMetaDataManager().allTable();
//        System.out.println(all);

//		UserEntity unique = sqlManager.unique(UserEntity.class, 1);
//		System.out.println(unique);

//		System.out.println("===更新后");
//		UserEntity user  = sqlManager.unique(UserEntity.class,1);
//		user.setName("ok123");
//		sqlManager.updateById(user);
//		System.out.println(unique);

//		System.out.println("查询部门为1得到所有用户");
//		UserEntity template = new UserEntity();
//		template.setDepartmentId(1);
//		List<UserEntity> list = sqlManager.template(template);
//		list.forEach(System.out::println);

		System.out.println("执行SQL");

//		String sql = "select * from sys_user where id=?";
//		Integer id  = 1;
//		SQLReady sqlReady = new SQLReady(sql,new Object[]{id});
//		List<UserEntity> userEntities = sqlManager.execute(sqlReady,UserEntity.class);
//		System.out.println(userEntities);

		//sql语句修改
//		String selectSql = "select * from  department";
//		SQLReady sqlReady = new SQLReady(selectSql);
//		List<DepartmentEntity> execute = sqlManager.execute(sqlReady, DepartmentEntity.class);
//		System.out.println(execute);
//
//		String updateSql = "update department set name=? where id =?";
//		String name="lijz";
//		SQLReady updateSqlReady = new SQLReady(updateSql,new Object[]{name,id});
//		sqlManager.executeUpdate(updateSqlReady);
//
//		DepartmentEntity departmentEntity = new DepartmentEntity();
//		List<DepartmentEntity> template = sqlManager.template(departmentEntity);
//		System.out.println(template);


		/**
		 * 		执行模板SQL
		 * 		像MyBatis那样，BeetlSQL 支持模板SQL。
		 */

//		String sql = "select * from sys_user where department_id=#{id} and name=#{name}";
//		UserEntity paras = new UserEntity();
//		paras.setDepartmentId(1);
//		paras.setName("lijz");
//		List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);


		//或者使用Map作为参数
//		String sql = "select * from sys_user where department_id=#{myDeptId} and name=#{myName}";
//		Map paras = new HashMap();
//		paras.put("myDeptId",1);
//		paras.put("myName","lijz");
//		List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);



		//使用Beetl模板语句
//		String sql = "select * from sys_user where 1=1 \n" +
//				"-- @if(isNotEmpty(myDeptId)){\n" +
//				"   and department_id=#{myDeptId}\n" +
//				"-- @}\n" +
//				"and name=#{myName}";
//
//
//		Map paras = new HashMap();
//		paras.put("myDeptId",1);
//		paras.put("myName","lijz");
//		List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);

		/**
		 * 使用Query
		 * 通常业务代码需要根据一定逻辑查询数据库，可以使用Query构造较为复杂的单表条件而避免写SQL
		 */

//		Query<UserEntity> query = sqlManager.query(UserEntity.class);
//		List<UserEntity> select = query.andEq("department_id", 1).andIsNotNull("name").select();
//		System.out.println(select);

		/**
		 * 使用LambdaQuery，能很好的支持数据库重构
		 */

		LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class);
		List<UserEntity> entities = query.andEq(UserEntity::getName,"bear")
				.andIsNotNull(UserEntity::getName).select();


		/**
		 * 使用Mapper
		 * BeetlSQL3更为推荐的使用Mapper，而不是SQLManager，SQLManger是更为底层的API，使用Mapper能更容易的维护业务代码
		 * BaseMapper是BeetlSQL3提供的一个接口，内置了大量CRUD方法。
		 */
//		UserMapper mapper = sqlManager.getMapper(UserMapper.class);
//		UserEntity me = mapper.unique(1);
//		System.out.println(me);
//
//		me.setName("newName");
//		mapper.updateById(me);
//		System.out.println(me);


		//其他常用的方法在SQLManager也有,BaseMapper有如下内置方法

//		SqlId id = SqlId.of("user","select");
//		Map map = new HashMap();
//		List<UserEntity> list = sqlManager.select(id,UserEntity.class,map);
//		System.out.println(list);
	}

}